# -*- coding=utf-8 -*-

from sqlmodel import create_engine, Session, select,SQLModel, Field
from typing import Optional
import pandas as pd
from pathlib import Path
from datetime import datetime, timedelta


# 复用之前的模型定义
class Record(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    类型: str
    顾客代码: Optional[str] = Field(default=None, alias="顾客代码")
    下单时间: Optional[str] = None
    销售回复出货时间: Optional[str] = None
    实际出货时间: Optional[str] = None
    采购单号: Optional[str] = None
    中聚订单号: Optional[str] = None
    电池型号: Optional[str] = None
    容量mash: Optional[float] = None
    技术图纸: Optional[str] = None
    带电量: Optional[float] = None
    下单数量: Optional[int] = None
    实发数量: Optional[int] = None
    入库数量: Optional[int] = None
    单价: Optional[float] = None
    总价: Optional[float] = None
    备注: Optional[str] = None
    来源: str  

def export_to_excel(output_path: str) -> None:
    # 创建数据库引擎
    engine = create_engine("sqlite:///msbase.db")
    
    # # 查询所有数据
    # with Session(engine) as session:
    #     records = session.exec(select(Record)).all()

    # 查询所有数据并按id倒序排列
    with Session(engine) as session:
        records = session.exec(select(Record).order_by(Record.id.desc())).all()
    
    # 转换数据到DataFrame
    data = []
    for record in records:
        row = record.dict()
        # row["顾客代码"] = row.pop("顾客代码")     # 处理字段别名重命名
        # 格式化日期字段
        for date_field in ["下单时间", "销售回复出货时间", "实际出货时间"]:
            if row[date_field]:
                original_value = row[date_field]
                formatted_date = None
                
                try:
                    # 尝试解析为Excel序列号
                    excel_num = float(original_value)
                    if excel_num > 0:
                        # Excel序列号转日期
                        date_obj = datetime(1899, 12, 30) + timedelta(days=excel_num)
                        formatted_date = date_obj.strftime("%Y/%m/%d")
                except (ValueError, TypeError):
                    pass
                
                if not formatted_date:
                    try:
                        # 尝试解析 YYYY.MM.DD 格式
                        date_obj = datetime.strptime(original_value, "%Y.%m.%d")
                        formatted_date = date_obj.strftime("%Y/%m/%d")
                    except ValueError:
                        pass
                
                if not formatted_date:
                    try:
                        # 尝试解析 YY.MM.DD 格式 (假设2000年后)
                        parts = original_value.split('.')
                        if len(parts) == 3:
                            year, month, day = parts
                            if len(year) == 2:
                                year = '20' + year  # 假设年份为2000后
                            date_obj = datetime(int(year), int(month), int(day))
                            formatted_date = date_obj.strftime("%Y/%m/%d")
                    except (ValueError, IndexError):
                        pass
                
                if not formatted_date:
                    try:
                        # 尝试其他常见格式
                        date_obj = datetime.strptime(original_value, "%Y-%m-%d %H:%M:%S")
                        formatted_date = date_obj.strftime("%Y/%m/%d")
                    except ValueError:
                        try:
                            date_obj = datetime.strptime(original_value, "%Y-%m-%d")
                            formatted_date = date_obj.strftime("%Y/%m/%d")
                        except ValueError:
                            # 如果无法解析，保持原始值
                            formatted_date = original_value
                
                # 更新格式化后的日期
                row[date_field] = formatted_date
        data.append(row)
    
    df = pd.DataFrame(data)
    
    # 调整列顺序
    columns_order = [
        "类型", "顾客代码", "下单时间", "销售回复出货时间", "实际出货时间",
        "采购单号", "中聚订单号", "电池型号", "容量mash", "技术图纸",
        "带电量", "下单数量", "实发数量", "入库数量", "单价", "总价", "备注","来源"
    ]
    df = df[columns_order]  #通过df[columns_order]重新选择列，就完成了 DataFrame 列顺序的调整。
    
    # 创建Excel写入对象
    with pd.ExcelWriter(output_path, engine="openpyxl") as writer:
        # 添加表头说明
        header_df = pd.DataFrame([["数据说明："] + [""]*(len(columns_order)-1)])
        header_df.to_excel(
            writer,
            sheet_name="Sheet1",
            index=False,
            header=False,
            startrow=0
        )
        
        # 添加空行
        pd.DataFrame().to_excel(
            writer,
            sheet_name="Sheet1",
            index=False,
            header=False,
            startrow=1
        )
        
        # 写入数据（从第三行开始）
        df.to_excel(
            writer,
            sheet_name="Sheet1",
            index=False,
            header=True,
            startrow=2
        )
        
        # 获取工作表对象进行格式调整
        worksheet = writer.sheets["Sheet1"]

        # 设置列宽（示例）
        for col_idx, col_name in enumerate(df.columns):
            width = max(len(str(col_name)), 12)
            worksheet.column_dimensions[chr(65 + col_idx)].width = width

def main():
    # 生成带时间戳的文件名
    timestamp = datetime.now().strftime("%Y%m%d_%H%M")
    output_file = f"account_data_{timestamp}.xlsx"
    
    try:
        export_to_excel(output_file)
        print(f"数据成功导出到：{Path(output_file).resolve()}")
    except Exception as e:
        print(f"导出失败：{str(e)}")

if __name__ == "__main__":
    main()